package org.timeismoney.payment.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.timeismoney.payment.dao.PaymentRecordDao;
import org.timeismoney.payment.model.PaymentRecord;

public class PaymentRecordDaoImpl extends GenericDaoImpl<PaymentRecord, Integer> implements
		PaymentRecordDao {

	public PaymentRecordDaoImpl() {
		super();
		setClazz(PaymentRecord.class);
	}
	
	public void batchInsertRecords(final List<PaymentRecord> paymentRecords){
		getHibernateTemplate().execute(new HibernateCallback() {
			
			@SuppressWarnings("deprecation")
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Connection conn = session.connection();
				PreparedStatement ps = conn.prepareStatement("insert into payment_record(school_year_no,stu_no,item_payment_no,payment_record_no,payment,success,create_date) " +
						"values (?,?,?,?,?,?,?)");
				for (PaymentRecord paymentRecord : paymentRecords) {
					ps.setInt(1, paymentRecord.getSchoolYear().getSchoolYearNo());
					ps.setString(2, paymentRecord.getStudent().getStuNo());
					ps.setInt(3, paymentRecord.getItemPaymentDetail().getItemPaymentNo());
					ps.setString(4, paymentRecord.getPaymentRecordNo());
					ps.setBigDecimal(5, paymentRecord.getPayment());
					ps.setInt(6, 0);
					ps.setTimestamp(7, new Timestamp(paymentRecord.getCreateDate().getTime()));
					ps.addBatch();
				}
				ps.executeBatch();
				return null;
			}
		});
	}

	public void batchUpdateRecords(String paymentRecordNo, java.util.Date now) {
		getHibernateTemplate().bulkUpdate("update PaymentRecord set success=true, success_date=? where paymentRecordNo=?", new Object[]{now,paymentRecordNo});
	}

	public BigDecimal getTotalPaySuccessByStuNoAndYearNo(final String stuNo,
			final Integer yearNo) {
		return (BigDecimal) getHibernateTemplate().execute(
				new HibernateCallback() {

					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						return session
								.createQuery(
										"select sum(p.payment) from PaymentRecord p where p.student.stuNo=? and p.schoolYear.schoolYearNo=? and p.success=true")
								.setString(0, stuNo).setInteger(1, yearNo)
								.uniqueResult();
					}
				});
	}

	public void deleteByStuNo(String stuNo) {
		getHibernateTemplate().bulkUpdate(
				"delete from PaymentRecord where student.stuNo=?", stuNo);
	}

	public void deleteByItemPaymentNo(int itemPaymentNo) {
		getHibernateTemplate().bulkUpdate(
				"delete from PaymentRecord where itemPaymentDetail.itemPaymentNo=?", itemPaymentNo);
	}

	public void deleteBySchoolYearNo(Integer schoolYearNo) {
		getHibernateTemplate().bulkUpdate(
				"delete from PaymentRecord where schoolYear.schoolYearNo=?", schoolYearNo);
	}

	public void deleteByItemNo(Integer itemNo) {
		getHibernateTemplate().bulkUpdate(
				"delete from PaymentRecord p where p.itemPaymentDetail in (from ItemPaymentDetail i where i.item.itemNo = ?)", itemNo);
	}

}
